<?php
require_once ('../../libs/smarty.inc');
require_once ('../../libs/smt_variables.php');
require_once ('../../libs/Class_DB.php');
require_once ('../../libs/Class_ERROR.php');
require_once ('../../libs/login.inc.php');
require_once ('../../libs/const.php');
require_once ('../../libs/page.php');
require_once ('../../libs/new_smt_variables.php');

if (! in_array ( "H", $_SESSION ['ADMIN_LOGIN'] ['AUTH_CD'] ))
	exit ();

$company_name = $_GET ["company_name"];
$katakana = $_GET ["katakana"];
$branch_name = $_GET ["branch_name"];
$sal_manager = $_GET ["sal_manager"];
$r_date1 = $_GET ["r_date1"];
$r_date2 = $_GET ["r_date2"];
$u_date1 = $_GET ["u_date1"];
$u_date2 = $_GET ["u_date2"];

$price_no = $_GET ['price_no'];
$pro_name = $_GET ['pro_name'];
$category = $_GET ['category'];
$m_date1 = $_GET ['m_date1'];
$m_date2 = $_GET ['m_date2'];
$u_date3 = $_GET ["u_date3"];
$u_date4 = $_GET ["u_date4"];
$producer = $_GET ['producer'];
$status1 = $_GET ['status1'];
$status2 = $_GET ['status2'];
$status3 = $_GET ['status3'];
$status4 = $_GET ['status4'];
$status5 = $_GET ['status5'];
$status6 = $_GET ['status6'];
$e_date1 = $_GET ['e_date1'];
$e_date2 = $_GET ['e_date2'];
$u_date5 = $_GET ["u_date5"];
$u_date6 = $_GET ["u_date6"];

$db = new Class_DB ();
$sql = "SELECT * FROM M_STAFF";
$res = $db->query ( $sql );
$staff = array ();
while ( $row = mysql_fetch_array ( $res, MYSQL_ASSOC ) ) {
	$staff [$row ['staff_id']] = $row ['staff_name'];
}

// SQLクエリー
$sql = "SELECT A.company_name,A.katakana,A.branch_name,A.category,A.sal_manager,A.payment_term AS pt,A.consumer_id,B.*,C.shop_name,C.refer_price,C.discount,C.quantity,C.s_unit,C.cost_price FROM p_consumer A,p_consumer_head B,p_consumer_detail C,p_shop D WHERE 1 = 1 and B.price_no=C.price_no and C.shop_id=D.shop_id and A.consumer_id = B.consumer_id  ";

if ($company_name != null) {
	$sql = $sql . " and A.company_name like '%" . $company_name . "%'";
}
if ($katakana != null) {
	$sql = $sql . " and A.katakana like '%" . $katakana . "%'";
}
if ($branch_name != null) {
	$sql = $sql . " and A.branch_name like '%" . $branch_name . "%'";
}
if ($sal_manager != null) {
	$sql = $sql . " and A.sal_manager like '%" . $sal_manager . "%'";
}
if ($r_date1 != null && $r_date2 != null) {
	$sql = $sql . " and A.r_date BETWEEN '" . $r_date1 . "' and '" . $r_date2 . "'";
}
if ($r_date1 != null && $r_date2 == null) {
	$sql = $sql . " and A.r_date >= '" . $r_date1 . "'";
}
if ($r_date1 == null && $r_date2 != null) {
	$sql = $sql . " and A.r_date < '" . $r_date2 . "'";
}
if ($u_date1 != null && $u_date2 != null) {
	$sql = $sql . " and A.u_date BETWEEN '" . $u_date1 . "' and '" . $u_date2 . "'";
}
if ($u_date1 != null && $u_date2 == null) {
	$sql = $sql . " and A.u_date >= '" . $u_date1 . "'";
}
if ($u_date1 == null && $u_date2 != null) {
	$sql = $sql . " and A.u_date < '" . $u_date2 . "'";
}

if ($price_no != null) {
	$sql = $sql . " and B.price_no like '" . $price_no . "%'";
}
if ($pro_name != null) {
	$sql = $sql . " and B.pro_name like '%" . $pro_name . "%'";
}
if ($category != null) {
	$sql = $sql . " and D.type like '%" . $category . "%'";
}
if ($m_date1 != null && $m_date2 != null) {
	$sql = $sql . " and B.m_date BETWEEN '" . $m_date1 . "' and '" . $m_date2 . "'";
}
if ($m_date1 != null && $m_date2 == null) {
	$sql = $sql . " and B.m_date >= '" . $m_date1 . "'";
}
if ($m_date1 == null && $m_date2 != null) {
	$sql = $sql . " and B.m_date <= '" . $m_date2 . "'";
}
if ($u_date3 != null && $u_date4 != null) {
	$sql = $sql . " and B.u_date BETWEEN '" . $u_date3 . "' and '" . $u_date4 . "'";
}
if ($u_date3 != null && $u_date4 == null) {
	$sql = $sql . " and B.u_date >= '" . $u_date3 . "'";
}
if ($u_date3 == null && $u_date4 != null) {
	$sql = $sql . " and B.u_date <= '" . $u_date4 . "'";
}
if ($producer != null) {
	$sql = $sql . " and B.producer like '%" . $producer . "%'";
}
$status = array ();
if ($status1 != null) {
	$status [] = $status1;
}
if ($status2 != null) {
	$status [] = $status2;
}
if ($status3 != null) {
	$status [] = $status3;
}
if ($status4 != null) {
	$status [] = $status4;
}
if ($status5 != null) {
	$status [] = $status5;
}
if ($status6 != null) {
	$status [] = $status6;
}
if (count ( $status ) > 0) {
	$sql = $sql . " and B.status in (" . implode ( ",", $status ) . ")";
}
if ($e_date1 != null && $e_date2 != null) {
	$sql = $sql . " and B.e_date BETWEEN '" . $e_date1 . "' and '" . $e_date2 . "'";
}
if ($e_date1 != null && $e_date2 == null) {
	$sql = $sql . " and B.e_date >= '" . $e_date1 . "'";
}
if ($e_date1 == null && $e_date2 != null) {
	$sql = $sql . " and B.e_date <= '" . $e_date2 . "'";
}
if ($u_date5 != null && $u_date6 != null) {
	$sql = $sql . " and B.ok_date BETWEEN '" . $u_date5 . "' and '" . $u_date6 . "'";
}
if ($u_date5 != null && $u_date6 == null) {
	$sql = $sql . " and B.ok_date >= '" . $u_date5 . "'";
}
if ($u_date5 == null && $u_date6 != null) {
	$sql = $sql . " and B.ok_date <= '" . $u_date6 . "'";
}

$sql = $sql . "  order by B.u_date desc";

$res = $db->query ( $sql );
$data = array ();
while ( $row = mysql_fetch_array ( $res, MYSQL_ASSOC ) ) {
	
	// 1作成中 2 承認依頼中 3承認済み 4 承認却下 5客先OK 6客先NG
	if ($row ['status'] == 5) {
		$opt = "OK";
	} elseif ($row ['status'] == 6) {
		$opt = "NG";
	}
	$row ['opt'] = $opt;
	$row ['r_date'] = $row ['r_date'] == NULL ? "" : date ( 'Y/m/d', strtotime ( $row ['r_date'] ) );
	$row ['u_date'] = $row ['u_date'] == NULL ? "" : date ( 'Y/m/d', strtotime ( $row ['u_date'] ) );
	$row ['m_date'] = $row ['m_date'] == NULL ? "" : date ( 'Y/m/d', strtotime ( $row ['m_date'] ) );
	$row ['e_date'] = $row ['e_date'] == NULL ? "" : date ( 'Y/m/d', strtotime ( $row ['e_date'] ) );
	$row ['staff_name'] = $staff [$row ['staff_id']];
	$price_no=$row ['price_no'];
	
	if($row ['payment_term']==''){
		$row ['payment_term']=$row ['pt'];
	}
	
	$sql3 = "SELECT * FROM p_consumer_detail  WHERE price_no='$price_no'";
	$result3 = $db->query($sql3);
	$m1 = 0;
	while( $row3 = mysql_fetch_array($result3,MYSQL_ASSOC) ){
		$row3['money'] =number_format(intval($row3['refer_price']*$row3['discount']*$row3['quantity']));
		$row3['cmoney'] =(intval($row3['refer_price']*$row3['discount']*$row3['quantity']));
		/* $row3['m'] =(intval($row3['refer_price']*$row3['discount']*$row3['quantity']));
		$row3['m']=$row3['m']==0?1:$row3['m'];
		$row3['profit'] = intval((($row3['m']-$row3['cost_price']*$row3['quantity'])/$row3['m'])*100)."%";
		$row3['refer_price'] = intval($row3['refer_price']); */
		$m1 = $m1+$row3['cmoney'];
	}
	$m2=$m1*$row['tax'];
	$m3=$m1+$m2;
	
	$row['money'] =number_format(intval($row['refer_price']*$row['discount']*$row['quantity']));
	$row['m'] =(intval($row['refer_price']*$row['discount']*$row['quantity']));
	echo $row['cost_price'];
	$row['m']=$row['m']==0?1:$row['m'];
	$row['profit'] = intval((($row['m']-$row['cost_price']*$row['quantity'])/$row['m'])*100)."%";
	$row['refer_price'] = intval($row['refer_price']);
	$data [] = mb_convert_encoding ( sprintf ( "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\"",
			 $row ['price_no'], $row ['company_name'], $row ['producer'], $staff[$row['examiner']], $row ['sal_manager'], $row ['payment_term'], $make_price_option[$row ['pro_name']], $opt, $row ['save_address'], $row ['save_date'], $row ['period_remark'], $row ['period_str'], $row ['period_end'], $row ['dateestimated']
			, $row ['shipping'], $row ['shop_name'], $row ['refer_price'], $row ['discount'], $row ['quantity'], $row ['s_unit'], $row ['money'], $row ['profit'], $m1, $m2,$m3, $row ['remark'], $row ['special_reason'], $row ['m_date'], $row ['u_date'], $row ['e_date'] ), "SJIS-win", "utf8" );
}

$head = "見積番号,得意先名,作成者,承認者,セールス担当者,支払条件,件名,客先同意,納入場所,納入期日,適用期間,開始日,終了日,見積有効期限,送料,商品名,単価,掛率,数量,単位,金額,利益率,税抜合計金額,消費税,税込合計金額,備考,特価理由,作成日,更新日,承認日";

$filename = date ( "YmdHis" ) . '.csv';

$head = mb_convert_encoding ( $head, "SJIS-win", "utf8" );

header ( "Content-Type: application/octet-stream" );
header ( 'Content-Disposition: attachment; filename=' . $filename );

print $head;
print "\n";
print implode ( $data, "\n" );

?>